Skip Headers

Oracle® Transparent Gateway for DRDA Installation and User's Guide 10g Release 2 (10.2) for UNIX Part Number B16217-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 13 Developing Applications

Oracle Transparent Gateway for DRDA allows applications written for Oracle Database to access tables in a DRDA Database. This access can be virtually transparent by using synonyms or views of the DRDA tables accessed by a database link. However, fundamental SQL, data type, and semantic differences exist between the Oracle Database and DRDA Databases.

This chapter provides information that is specific to the 10.2 release of the Oracle Transparent Gateway for DRDA. This chapter contains the following sections:

- Gateway Appearance to Application Programs

- Using Oracle Stored Procedures with the Gateway

- Using DRDA Server Stored Procedures with the Gateway

- Database Link Behavior

- Oracle Database SQL Construct Processing

- Native Semantics

- DRDA Data type to Oracle Data type Conversion

- Passing Native SQL Statements through the Gateway

- Oracle Data Dictionary Emulation on a DRDA Server

- Defining the Number of DRDA Cursors

## Gateway Appearance to Application Programs

An application that is written to access information in a DRDA Database interfaces with an Oracle Integrating Server. When developing applications, keep the following information in mind:

- You must define the DRDA Database to the application by using of a database link that is defined in the Oracle Integrating Server. Your application should specify tables that exist on a DRDA database by using the name that is defined in the database link. For example, assume that a database link is defined so that it names the DRDA database link `DRDA``,` and also assume that an application needs to retrieve data from an Oracle Database and from the `DRDA` database. Use the following SQL statement (joining two tables together) in your application:

      SELECT EMPNO, SALARY
      FROM EMP L, EMPS@DRDA R
      WHERE L.EMPNO = R.EMPNO

  In this example, `EMP` is a table on an Oracle Database, and `EMPS` is a table on a DRDA Server. You can also define a synonym or a view on the DRDA Server table, and access the information without the database link suffix.

- You can read and write data to a defined DRDA database. `SELECT`, `INSERT`, `UPDATE`, and `DELETE` are all valid operations.

- A single transaction can write to one DRDA database and to multiple Oracle Databases.

- Single SQL statements, using `JOINs`, can refer to tables in multiple Oracle Databases, in multiple DRDA databases, or in both.

### Fetch Reblocking

Oracle Database supports fetch reblocking with the `HS_RPC_FETCH_REBLOCKING` parameter.

When the value of this parameter is set to `ON` (the default), the array size for `SELECT` statements is determined by the `HS_RPC_FETCH_SIZE` value. The `HS_RPC_FETCH_SIZE` parameter defines the number of bytes sent with each buffer from the gateway to the Oracle Database 10g. The buffer may contain one or more qualified rows from the DRDA Server. This feature can provide significant performance enhancements, depending on your application design, installation type, and workload.

The array size between the client and the Oracle Database 10g is determined by the Oracle application. Refer to Chapter 11, "Configuring the Gateway", for more information.

## Using Oracle Stored Procedures with the Gateway

The gateway stored procedure support is an extension of Oracle stored procedures. An Oracle stored procedure is a schema object that logically groups together a set of SQL and other PL/SQL programming language statements to perform a specific task. Oracle stored procedures are stored in the database for continued use. Applications use standard Oracle PL/SQL to call stored procedures.

Oracle stored procedures can be located in a local instance of Oracle Database and in a remote instance. Figure 13-1 illustrates two stored procedures: `oraproc1` is a procedure stored in the ORA1 Oracle instance, and `oraproc2` is a procedure stored in the ORA2 Oracle instance.

Figure 13-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment

Description of "Figure 13-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment "

To maintain location transparency in the application, a synonym can be created:

    CREATE SYNONYM oraproc2 FOR oraproc2@ora2;

After this synonym is created, the application no longer needs to use the database link specification to call the stored procedure in the remote Oracle instance.

In Figure 13-1, the second statement in `oraproc1` is used to access a table in the ORA2 instance. In the same way, Oracle stored procedures can be used to access DB2 tables through the gateway.

## Using DRDA Server Stored Procedures with the Gateway

The procedural feature of the gateway enables invocation of native DRDA Server stored procedures. In other words, the stored procedure is no longer defined in the Oracle Database, but, is defined in the DRDA Server (for example., DB2/OS390). Again, standard Oracle PL/SQL is used by the Oracle application to run the stored procedure.

After the stored procedure is defined to the DRDA Server (for example., DB2/OS390), the gateway is able to use the existing DRDA Server definition to run the procedure. The gateway does not require special definitions to call the DB2 stored procedure.

In Figure 13-2, an Oracle application calls the `empproc` stored procedure that is defined to the DRDA Server (for example., DB2/OS390).

Figure 13-2 Running DRDA Server Stored Procedures

Description of "Figure 13-2 Running DRDA Server Stored Procedures"

From the perspective of the application, running the DB2 stored procedure is no different from invoking a stored procedure at a remote Oracle Database instance.

### Oracle Application and DRDA Server Stored Procedure Completion

If an Oracle Application attempts to invoke a stored procedure in a DB2/OS390 database. For an Oracle application to call a DB2 stored procedure, it is first necessary to create the DB2 stored procedure on the DB2 system by using the procedures documented in the IBM reference document for DB2 for OS/390 SQL.

After the stored procedure is defined in DB2, the gateway is able to access the data using a standard PL/SQL call. For example, an employee name, John Smythe, is passed to the DB2 stored procedure `REVISE_SALARY`. The DB2 stored procedure retrieves the salary value from the DB2 database in order to calculate a new yearly salary for John Smythe. The revised salary that is returned as result is used to update the `EMP` table of Oracle Database:

    DECLARE
      INPUT VARCHAR2(15);
      RESULT NUMBER(8,2);
    BEGIN
      INPUT := 'JOHN SMYTHE';
      REVISE_SALARY@DB2(INPUT, RESULT);
      UPDATE EMP SET SAL = RESULT WHERE ENAME = INPUT;
    END;

When the gateway receives a call to run a stored procedure on the DRDA Server (for example DB2/OS390), it first does a lookup of the procedure name in the server catalog. The information that defines a stored procedure is stored in different forms on each DRDA Server. For example, DB2/OS390 V5.0 uses the table `SYSIBM.SYSPROCEDURES`, while DB2/OS390 V6.1 uses the table `SYSIBM.SYSROUTINES` and `SYSIBM.SYSPARMS`, and DB2/400 uses the table `QSYS2.SYSPROCS` and `QSYS2.SYSPARMS`. The gateway has a list of known catalogs to search, depending on the DRDA Server that is being accessed.

The search order of the catalogs is dependent on whether the catalogs support Location designators (such as `LUNAME` in `SYSIBM.SYSPROCEDURES`), and authorization or owner IDs (such as `AUTHID` in `SYSIBM.SYSPROCEDURES` or `OWNER` in `SYSIBM.SYSROUTINES`).

Some DRDA Servers allow blank or public authorization qualifiers. If the DRDA Server that is currently connected supports which form of qualification, then the gateway will apply those naming rules when searching for a procedure name in the catalog.

The matching rules will first search for a public definition, and then an owner qualified procedure name. For more detailed information, refer to the IBM reference document for DB2 for OS/390 SQL.

### Procedural Feature Considerations with DB2

The following are special considerations for using the procedural feature with the gateway:

- DB2 stored procedures do not have the ability to coordinate, commit, and rollback activity on recoverable resources such as IMS or Customer Information Control System (CICS) transactions. Therefore, if the DB2 stored procedure calls a CICS or IMS transaction, then it is considered a separate unit of work and does not affect the completion of the stored procedure. This means that if you are running a DB2 stored procedure from an Oracle application, and if this procedure calls a CICS or IMS transaction, then the gateway cannot recover data from any activity that occurred within the CICS or IMS transaction.

  For example, the CICS transaction could roll back a unit of work, but this does not prevent the gateway from committing other DB2 work contained within the DB2 stored procedure.

  Likewise, if the DB2 stored procedure updated an irrecoverable resource such as a video surveillance and monitoring (VSAM) file, then the gateway would consider this activity separate from its own recoverable unit of work.

- PL/SQL records cannot be passed as parameters when invoking a DB2 stored procedure.

- The gateway supports the `SIMPLE` linkage convention of DB2 stored procedures.The `SIMPLE` linkage convention means that the parameters that are passed to and from the DB2 stored procedure cannot be null.

## Database Link Behavior

A connection to the gateway is established through a database link when it is first used in an Oracle Database session. In this context, a connection refers to both the connection between the Oracle Integrating Server and the gateway and to the DRDA network connection between the gateway and the target DRDA database. The connection remains established until the Oracle Database session ends. Another session or user can access the same database link and get a distinct connection to the gateway and DRDA database.

Connections to the DRDA database can be limited in an advanced program-to-program communication (APPC) configuration in a parallel session limit, or by other factors, such as memory, gateway parameters, or DRDA Server resources. In a TCP/IP configuration, only resource limits (such as memory) or limits on the number of connections by the DRDA Server will limit the number of connections between the gateway and the DRDA Server.

## Oracle Database SQL Construct Processing

One of the most important features of the Oracle Open Gateways products is providing SQL transparency to the user and to the application programmer. Foreign SQL constructs can be categorized into four areas:

- Compatible

- Translated

- Compensated

- Native semantics

### Compatible SQL Functions

Oracle Integrating Server automatically forwards compatible SQL functions to the DRDA database. Where SQL constructs with the same syntax and meaning are on both Oracle Database and the DRDA database. These SQL constructs are forwarded unmodified. All of the compatible functions are column functions. Functions that are not compatible are either translated to an equivalent DRDA SQL function or are compensated (post-processed) by Oracle Database after the data is returned from the DRDA database.

### Translated SQL Functions

Translated functions have the same meaning but different names between the Oracle Integrating Server and the DRDA database. But all applications must use the Oracle function name. These SQL constructs that are supported with different syntax ( different function names) by the DRDA database, are automatically translated by the Oracle Database and then forwarded to the DRDA database. Oracle Integrating Server changes the function name before sending it to the DRDA database, in a manner that is transparent to your application.

### Compensated SQL Functions

Some advanced SQL constructs that are supported by Oracle Database may not be supported in the same manner, if at all, by the DRDA database. Compensated functions are those SQL functions that are not recognized by the DRDA Server. If a `SELECT` statement containing one of these functions is passed from the Oracle Integrating Server to the gateway, then the gateway removes the function before passing the SQL statement to the DRDA Server. The gateway passes the selected DRDA database rows to Oracle Integrating Server. Oracle Integrating Server applies the function.

#### Post-Processing

Oracle Database can compensate for a missing or incompatible function by automatically excluding the incompatible SQL construct from the SQL request that is forwarded to the DRDA database. Oracle Database then retrieves the necessary data from the DRDA database and applies the function. This process is known as post-processing.

The gateway attempts to pass all SQL functions to DRDA databases. However when a DRDA database does not support a function that is represented in the computation, the gateway changes that function. For example, if a program runs the following query against a DB2/OS390 database:

    SELECT COS(X_COOR) FROM TABLE_X;

Beacuse the database does not support many of the `COS` functions, the gateway changes the query to the following:

    SELECT X_COOR FROM TABLE_X;

All data in the `X_COOR` column of `TABLE_X` is passed from the DB2/OS390 database to the Oracle integrating server. After the data is moved to the Oracle integrating server, the `COS` function is performed.

If you are performing operations on large amounts of data that are stored in a DRDA database, then keep in mind that some functions require post-processing.

### Native Semantic SQL Functions

Some SQL functions that are normally compensated may also be overridden, through the Native Semantics facility. If a SQL function has been enabled for Native Semantics, then the function may be passed on to the DRDA database for processing, instead of being compensated (post-processed). If a SQL function is enabled for Native Semantics and is therefore passed on the DRDA database for processing, then the SQL function is processed natively in the DRDA database. Refer to "Native Semantics" for more information.

### DB2/OS390 SQL Compatibility

Table 13-1 describes how Oracle Database and the gateway handles SQL functions for a DB2/OS390

Table 13-1 SQL Compatibility, by Oracle SQL function

Oracle SQL Function | Compatible | Translated | Compensated | Native Semantics Candidate
--- | --- | --- | --- | ---
`ABS` | - | - | Yes | Yes
`ACO`S | - | - | Yes | Yes
`ADD_MONTHS` | - | - | Yes |
`ASCII` | - | - | Yes | Yes
`ASIN` | - | - | Yes | Yes
`ATAN` | - | - | Yes | Yes
`ATAN2` | - | - | Yes | Yes
`AVG` | Yes | - | - | -
`BITAND` | - | - | Yes | Yes
`CAST` | - | - | Yes | Yes
`CEIL` | - | CEILING | - | Yes
`CHARTOROWID` | - | - | Yes | -
`CHR` | - | - | Yes | Yes
`CONCAT` | Yes | - | - | -
`CONVERT` | - | - | Yes | Yes
`COS` | - | - | Yes | Yes
`COSH` | - | - | Yes | Yes
`COUNT`(*) | Yes | - | - | -
`COUNT` (DISTINCT colname) | Yes | - | - | -
`COUNT` (ALL colname) | Yes | - | - | COUNTCOL
`COUNT` (column) | Yes | - | - | COUNTCOL
`DECODE` | - | - | Yes | Yes
`DUMP` | - | - | Yes | Yes
`EXP` | - | - | Yes | Yes
`FLOOR` | Yes | - | - | Yes
`GREATEST` | - | - | Yes | Yes
`HEXTORAW` | - | - | Yes | Yes
`INITCAP` | - | - | Yes | Yes
`INSTR` | - | - | Yes | Yes
`INSTRB` | - | - | Yes | Yes
`LAST_DAY` | - | - | Yes | -
`LEAST` | - | - | Yes | Yes
`LENGTH` | - | - | Yes | Yes
`LENGTHB` | - | - | Yes | Yes
`LN` | - | - | Yes | Yes
`LOG` | - | - | Yes | Yes
`LOWER` | - | - | Yes | Yes
`LPAD` | - | - | Yes | Yes
`LTRIM` | - | - | Yes | Yes
`MAX` | Yes | - | - | -
`MIN` | Yes | - | - | -
`MOD` | - | - | Yes | Yes
`MONTHS_BETWEEN` | - | - | Yes | -
`NEW_TIME` | - | - | Yes | -
`NEXT_DAY` | - | - | Yes | -
`NLS_INITCAP` | - | - | Yes | Yes
`NLS_LOWER` | - | - | Yes | Yes
`NLS_UPPER` |  | - | Yes | Yes
`NLSSORT` | - | - | Yes | Yes
NVL |  | VALUE |  |
`NVL2` | - | - | Yes | Yes
`POWER` | - | - | Yes | Yes
`RAWTOHEX` | - | - | Yes | Yes
`REPLACE` | - | - | Yes | Yes
`REVERSE` | - | - | Yes | Yes
`ROUND` | - | - | Yes | Yes
`ROWIDTOCHAR` | - | - | Yes | -
`RPAD` | - | - | Yes | Yes
`RTRIM` | - | - | Yes | Yes
`SIGN` | - | - | Yes | Yes
`SIN` | - | - | Yes | Yes
`SINH` | - | - | Yes | Yes
SOUNDEX |  | - | Yes | -
`SQRT` | - | - | Yes | Yes
`STDDEV` | - | - | Yes | Yes
`SUBSTR` | - | - | Yes | Yes
`SUBSTRB` | - | - | Yes | Yes
`SUM` | Yes | - | - | -
`SYSDATE` | - | - | Yes |
`TAN` | - | - | Yes | Yes
`TANH` | - | - | Yes | Yes
`TO_CHAR` | - | - | Yes | -
`TO_DATE` | - | - | Yes | -
`TO_MULTI_BYTE` | - | - | Yes | -
`TO_NUMBER` | - | DECIMAL | - | Yes
`TO_SINGLE_BYTE` | - | - | Yes | -
`TRANSLATE` | - | - | Yes | Yes
`TRIM` | - | STRIP | Yes | Yes
`TRUNC` | - | - | Yes | Yes
`UID` | - | - | Yes | -
`UPPER` | - | - | Yes | Yes
`USER` | - | - | Yes | -
`USERENV` | - | - | Yes | -
`VARIANCE` | - | - | Yes | Yes
`VSIZE` | - | - | Yes | Yes

### DB2/Universal Database SQL Compatibility

The ways that Oracle Database and gateway handle SQL functions for a DB2/UDB database are shown in the following table:

Table 13-2 DB2/Universal Database SQL Compatibility, by Oracle SQL Function

Oracle SQL Function | Compatible | Translated | Compensated | Native Semantics Candidate
--- | --- | --- | --- | ---
`ABS` | Yes | - | - | Yes
`ACOS` | - | - | Yes | Yes
`ADD_MONTHS` | - | - | Yes | -
`ASCII` | - | - | Yes | Yes
`ASIN` | - | - | Yes | Yes
`ATAN` | - | - | Yes | Yes
`ATAN2` | - | - | Yes | Yes
`AVG` | Yes | - | - | -
`BITAND` | - | - | Yes | Yes
`CAST` | - | - | Yes | Yes
`CEIL` | - | CEILING | - | Yes
`CHARTOROWID` | - | - | Yes | -
`CHR` | Yes | - | - | Yes
`CONCAT` | Yes | - | - | -
`CONVERT` | - | - | Yes | Yes
`COS` | Yes | - | - | Yes
`COSH` | - | - | Yes | Yes
`COUNT`(*) | Yes | - | - | -
`COUNT` (DISTINCT colname) | Yes | - | - | -
`COU` `NT` (ALL colname) | Yes | - | - | COUNTCOL
`COUNT` (column) | Yes | - | - | COUNTCOL
`DECODE` | - | - | Yes | Yes
`DUMP` | - | - | Yes | Yes
`EXP` | Yes | - | - | Yes
`FLOOR` | Yes | - | - | Yes
`GREATEST` | - | - | Yes | Yes
`HEXTORAW` | - | - | Yes | Yes
`INITCAP` | - | - | Yes | Yes
`INSTR` | - | - | Yes | Yes
`INSTRB` | - | - | Yes | Yes
`LAST_DAY` | - | - | Yes | -
`LEAST` | - | - | Yes | Yes
`LENGTH` | - | - | Yes | Yes
`LENGTHB` | - | - | Yes | Yes
`LN` | Yes | - | v | Yes
`LOG` | - | - | Yes | Yes
`LOWER` | - | LCASE | - | Yes
`LPAD` |  | - | Yes | Yes
`LTRIM` | - | - | Yes | Yes
`MAX` | Yes | - | - | -
`MIN` | Yes | - | - | -
`MOD` | Yes | - | - | Yes
`MONTHS_BETWEEN` | - | - | Yes | -
`NEW_TIME` | - | - | Yes | -
`NEXT_DAY` | Yes | - | Yes | -
`NLS_INITCAP` | - | - | Yes | Yes
`NLS_LOWER` | - | - | Yes | Yes
`NLS_UPPER` |  |  | Yes | Yes
`NLSSORT` | - | - | Yes | Yes
`NVL` | - | VALUE | - | -
`NVL2` | - | - | Yes | Yes
`POWER` | Yes | - | - | Yes
`RAWTOHEX` | - | - | Yes | Yes
`REPLACE` | Yes | - | - | Yes
`REVERSE` | - | - | Yes | Yes
`ROUND` | Yes | - | - | Yes
`ROWIDTOCHAR` |  | - | Yes | -
`RPAD` |  |  | Yes | Yes
`RTRIM` | - | - | Yes | Yes
`SIGN` | Yes | - | - | Yes
`SIN` | Yes | - | - | Yes
`SINH` | - | - | Yes | Yes
`SOUNDEX` | - | - | Yes | -
`SQRT` | Yes | - | - | Yes
`STDDEV` | - | - | Yes | Yes
`SUBSTR` | - | - | Yes | Yes
`SUBSTRB` | - | - | Yes | Yes
`SUM` | Yes | - | - | -
`SYSDATE` | - | - | Yes | -
`TAN` | Yes | - | - | Yes
`TANH` | - | - | Yes | Yes
`TO_CHAR` | - | - | Yes | -
`TO_DATE` | - | - | Yes | -
`TO_MULTI_BYTE` | - | - | Yes | -
`TO_NUMBER` | - | DECIMAL | - | Yes
`TO_SINGLE_BYTE` | - | - | Yes | -
`TRANSLATE` | - | - | Yes | Yes
`TRIM` | - | - | Yes | Yes
`TRUNC` | Yes | - | - | Yes
`UID` | - | - | Yes | -
`UPPER` | - | UCASE | v | Yes
`USER` | - | - | Yes | -
`USERENV` | - | - | Yes | -
`VARIANCE` | - | - | Yes | Yes
`VSIZE` | - | - | Yes | Yes

### DB2/400 SQL Compatibility

The ways that Oracle Database and gateway handle SQL functions for a DB2/400 database are shown in the following table:

Table 13-3 DB2/400 SQL Compatibility, by Oracle SQL Function

Oracle SQL Function | Compatible | Translated | Compensated | Native Semantics Candidate
--- | --- | --- | --- | ---
`ABS` | - | ABSVAL | - | Yes
`ACOS` | - | - | Yes | Yes
`ADD_MONTHS` | - | - | Yes | -
`ASCII` | - | - | Yes | Yes
ASIN | - | - | Yes | Yes
`ATAN` | - | - | Yes | Yes
`ATAN2` | - | - | Yes | Yes
`AVG` | Yes | - | - | -
`BITAND` | - | - | Yes | Yes
`CAST` | - | - | Yes | Yes
`CEIL` | - | CEILING | - | Yes
`CHARTOROWID` | - | - | Yes | -
CHR | - | - | Yes | Yes
`CONCAT` | Yes | - | - | -
`CONVERT` | - | - | Yes | Yes
`COS` | Yes | - | - | Yes
`COSH` | Yes | - | - | Yes
`COUNT`(*) | Yes | - | - | -
`COUNT` (DISTINCT colname) | Yes | - | - | -
`COUNT` (ALL colname) | Yes | - | - | COUNTCOL
`COUNT` (column) | Yes | - | - | COUNTCOL
`DECODE` | - | - | Yes | Yes
`DUMP` | - | - | Yes | Yes
`EXP` | Yes | - | - | Yes
`FLOOR` | Yes | - | - | Yes
`GREATEST` | - | - | Yes | Yes
`HEXTORAW` | - | - | Yes | Yes
`INITCAP` | - | - | Yes | Yes
INSTR | - | - | Yes | Yes
`INSTRB` | - | - | Yes | Yes
`LAST_DAY` | - | - | Yes | -
`LEAST` | - | - | Yes | Yes
`LENGT`H | - | - | Yes | Yes
`LENGTHB` | - | - | Yes | Yes
`LN` | Yes | - | - | Yes
`LOG` | - | - | Yes | Yes
`LOWER` | - | - | Yes | Yes
`LPAD` | - | - | Yes | Yes
`LTRIM` | - | - | Yes | Yes
`MAX` | Yes | - | - | -
`MIN` | Yes | - | v | -
`MOD` | - | - | Yes | Yes
`MONTHS_BETWEEN` | - | - | Yes |
`NEW_TIME` | - | - | Yes |
`NEXT_DAX` | - | - | Yes |
`NLS_INITCAP` | - | - | Yes | Yes
`NLS_LOWER` | - | - | Yes | Yes
`NLS_UPPER` | - | - | Yes | Yes
`NLSSORT` | - | - | Yes | Yes
`NVL` | - | VALUE | - | -
`NVL2` | - | - | Yes | Yes
`POWER` | - | -- | Yes | Yes
`RAWTOHEX` | - | - | Yes | Yes
`REPLACE` | - | - | Yes | Yes
`REVERSE` | - | - | Yes | Yes
`ROUND` | - | - | Yes | Yes
`ROWIDTOCHAR` | - | - | Yes | -
`RPAD` | - | - | Yes | Yes
`RTRIM` | - | - | Yes | Yes
`SIGN` | - | - | Yes | Yes
`SIN` | Yes | - | - | Yes
`SINH` | Yes | - | - | Yes
`SOUNDEX` | - | - | Yes | -
`SQRT` | Yes | - | - | Yes
`STDDEV` | Yes | v | - | Yes
`SUBSTR` | - | - | Yes | Yes
`SUBSTRB` | - | - | Yes | Yes
`SUM` | Yes | - | - | -
`SYSDATE` | - | - | Yes | -
`TAN` | Yes | - | - | Yes
`TANH` | Yes | - | - | Yes
`TO_CHAR` | - | - | Yes | -
`TO_DATE` | - | - | Yes | -
`TO_MULTI_BYTE` | - | - | Yes | -
`TO_NUMBER` | - | DECIMAL | - | Yes
`TO_SINGLE_BYTE` | - | - | Yes | -
`TRANSLATE` | - | - | Yes | Yes
`TRIM` | - | - | Yes | Yes
`TRUN` `C` | - | - | Yes | Yes
`UID` | - | - | Yes | -
`UPPER` | - | TRANSLATE | - | Yes
`USER` | - | - | Yes | -
`USERENV` | - | - | Yes | -
`VARIANCE` | - | VAR | - | Yes
`VSIZE` | - | - | Yes | Yes

### DB2/VM SQL Compatibility

The ways that Oracle Database and gateway handle SQL functions for a DB2/VM database are shown in the following table:

Table 13-4 DB2/VM SQL Compatibility, by Oracle SQL Function

Oracle SQL Function | Compatible | Translated | Compensated | Native Semantics Candidate
--- | --- | --- | --- | ---
ABS | - | - | Yes | Yes
ACOS | - | - | Yes | Yes
ADD_MONTHS | - | - | Yes | -
ASCII | - | - | Yes | Yes
ASIN | - | - | Yes | Yes
ATAN |  |  | Yes | Yes
ATAN2 | - | - | Yes | Yes
AVG | Yes | - | - | -
BITAND | - | - | Yes | Yes
`CAST` | - | - | Yes | Yes
`CEIL` | - | - | - | Yes
CHARTOROWID | - | - | Yes | -
`CHR` | - | - | Yes | Yes
`CONCAT` | Yes | - | - | -
`CONVERT` | - | - | Yes | Yes
`COS` | - | - | Yes | Yes
`COSH` | - | - | Yes | Yes
`COUNT`(*) | Yes | - | - | -
`COUNT` (DISTINCT colname) | Yes | - | - | -
`COUNT` (ALL colname) | - | - | Yes | -
`COUNT` (COLUMN) | - | - | Yes | -
`DECODE` | - | - | Yes | Yes
`DUMP` | - | - | Yes | Yes
`EXP` | - | - | Yes | Yes
`FLOOR` | - | - | - | Yes
`GREATEST` | - | - | Yes | Yes
`HEXTORAW` | - | - | Yes | Yes
`INITCAP` | - | - | Yes | Yes
`INSTR` | - | - | Yes | Yes
`INSTRB` | - | - | Yes | Yes
`LAST_DAY` | - | - | Yes | -
`LEAST` | - | - | Yes | Yes
`LENGTH` | - | - | Yes | Yes
`LENGTHB` | - | - | Yes | Yes
`LN` | - | - | Yes | Yes
`LOG` | - | - | Yes | Yes
`LOWER` | - | - | Yes | Yes
`LPAD` | - | - | Yes | Yes
`LTRIM` | - | - | Yes | Yes
`MAX` | Yes | - | - | -
`MIN` | Yes | - | - | -
`MOD` | - | - | Yes | Yes
`MONTHS_BETWEEN` | - | - | Yes | -
`NEW_TIME` | - | - | Yes | -
`NEXT_DAY` | - | - | Yes | -
`NLS_INITCAP` | - | - | Yes | Yes
`NLS_LOWER` | - | - | Yes | Yes
`NLS_UPPER` | - | - | Yes | Yes
`NLSSORT` | - | - | Yes | Yes
`NVL` | - | VALUE | - | -
`NVL2` | - |  | Yes | Yes
`POWER` | - | - | Yes | Yes
`RAWTOHEX` | - | - | Yes | Yes
`REPLACE` | - | - | Yes | Yes
`REVERSE` | - | - | Yes | Yes
`ROUND` | - | - | Yes | Yes
`ROWIDTOCHAR` | - | - | Yes | -
`RPAD` | - | - | Yes | Yes
`RTRIM` | - | - | Yes | Yes
`SIGN` | - | - | Yes | Yes
`SIN` | - | - | Yes | Yes
`SINH` | - | - | Yes | Yes
`SOUNDEX` | - | - | Yes | -
`SQRT` | - | - | Yes | Yes
`STDDEV` | - | - | Yes | Yes
`SUBSTR` | - | - | Yes | Yes
`SUBSTRB` | - | - | Yes | Yes
`SUM` | Yes | - | - | -
`SYSDATE` | - | - | Yes | -
`TAN` | - | - | Yes | Yes
`TANH` | - | - | Yes | Yes
`TO_CHAR` | - | - | Yes | -
`TO_DATE` | - | - | Yes | -
`TO_MULTI_BYTE` | - | - | Yes | -
`TO_NUMBER` | - | - | Yes | Yes
`TO_SINGLE_BYTE` | - | - | Yes | -
`TRANSLATE` | - | - | Yes | Yes
`TRIM` | - | - | Yes | Yes
`TRUNC` | - | - | Yes | Yes
`UID` | - | - | Yes | -
`UPPER` | - | - | Yes | Yes
`USER` | - | - | Yes | -
`USERENV` | - | - | Yes | -
`VARIANCE` | - | - | Yes | Yes
`VSIZE` | - | - | Yes | Yes

## Native Semantics

Because some of the advanced SQL constructs that are supported by Oracle Database may not be supported in the same manner by the DRDA database, the Oracle Database compensates for the missing or incompatible functionality by post-processing the DRDA database data with Oracle Database functionality

See Also: "Oracle Database SQL Construct Processing" for more information

This feature provides maximum transparency, but may impact performance. In addition, new versions of a particular DRDA database may implement previously unsupported functions or capabilities, or they may change the supported semantics as to make them more compatible with Oracle Database functions.

Some of DRDA Servers also provide support for user-defined functions. The user may choose to implement Oracle Database functions natively (in the DRDA database). This enables the DRDA Server to pass the function to the underlying database implementation (for example DB2). Native Semantics provides a method of enabling specific capabilities to be processed natively by the DRDA Server.

Various considerations must be taken into account when enabling the Native Semantic feature of a particular function because Native Semantics has advantages and disadvantages, which are typically a trade-off between transparency and performance. One such consideration is the transparency of data coercions. Oracle Database provides coercion (implicit data conversion) for many SQL functions. This means that if the supplied value for a particular function is not correct, then Oracle Database will coerce the value (change it to the correct value type) before processing it. However, with the Native Semantic feature enabled, the value (exactly as provided) will be passed to the DRDA Server for processing. In many cases, the DRDA Server will not be able to coerce the value to the correct type and will generate an error.

Another consideration involves the compatibility of parameters to a particular SQL function. For instance, Oracle Database implementation of `SUBSTR` allows negative values for the string index, whereas most DRDA Server implementations of `SUBSTR` do not allow negative values for the string index. However, if the application is implemented to invoke `SUBSTR` in a manner that is compatible with the DRDA Server, then the function will behave the same in either Oracle Database or the DRDA Server.

Another consideration is that the processing of a function at the DRDA Server may not be desirable due to resource constraints in that environment.

Refer to the DRDA_CAPABILITY parameter for details on enabling or disabling these capabilities. Refer to the Oracle Database SQL Reference for Oracle Database format of the following capabilities.

### SQL Functions That Can Be Enabled

The following list contains SQL functions that are disabled (OFF) by default. They can be enabled (turned ON) as an option:

Table 13-5 List of SQL Functions That Can Be Enabled

Function Name | Function Name | Function Name | Function Name
--- | --- | --- | ---
`ABS` | `ACOS` | `ASCII` | `ASIN`
`ATAN` | `ATAN2` | `BITAND` | `CAST`
`CEIL` | `CHR` | `CONVERT` | `COS`
`COSH` | `COUNTCOL` | `DECODE` | `DUMP`
`EXP` | `FLOOR` | `GREATEST` | `HEXOTRAW`
`INITCAP` | `INSTR` | `INSTRB` | `LEAST`
`LENGTH` | `LENGTHB` | `LN` | `LOG`
`LOWER` | `LPAD` | `LTRIM` | `MOD`
`NLS_INITCAP` | `NLS_UPPER` | `NLS_LOWER` | `NLSSORT`
`NVL2` | `POWER` | `RAWTOHEX` | `REPLACE`
`REVERSE` | `ROUND` | `RPAD` | `RTRIM`
`SIGN` | `SIN` | `SINH` | `SQRT`
`STDDEV` | `SUBSTR` | `SUBSTRB` | `TAN`
`TANH` | `TO_NUMBER` | `TRANSLATE` | `TRIM`
`TRUNC` | `UPPER` | `VARIANCE` | `VSIZE`

### SQL Functions That Can Be Disabled

The following SQL functions are enabled (ON) by default. :

- `GROUPBY`

- `HAVING`

- `ORDERBY`

- `WHERE`

`ORDERBY` controls sort order, which may differ at various sort locations. For example, with `ORDERBY` `ON`, a DB2 sort would be based on Extended Binary Coded Decimal Interchange Code (`EBCDIC)` sorting order, whereas with `ORDERBY` `OFF`, an Oracle Database sort would be based on ASCII sorting order.

The other three functions, `GROUPBY`, `HAVING`, and `WHERE`, can take additional processing time. If you need to minimize the use of expensive resources, then you should choose the settings of these functions so that the processing is performed with cheaper resource. The above listed functions can also be disabled.

### SQL Set Operators and Clauses

The `WHERE` and `HAVING` clauses are compatible for all versions of the DRDA Server. This means that these clauses are passed unchanged to the DRDA Server for processing. Whether clauses `GROUP BY` and `ORDER BY` are passed to the DRDA Server, or compensated by Oracle Database, is determined by the Native Semantics Parameters (see the previous section).

The set operators `UNION` and `UNION ALL` are compatible for all versions of the DRDA Server, meaning that they are passed unchanged to the DRDA Server for processing. The set operators `INTERSECT` and `MINUS` are compensated on all versions of the DRDA Server except DB2/UDB. For DB2/UDB, `INTERSECT` is compatible and `MINUS` is translated to `EXCEPT`.

## DRDA Data type to Oracle Data type Conversion

To move data between applications and the database, the gateway binds data values from a host variable or literal of a specific data type to a data type understood by the database. Therefore, the gateway maps values from any version of the DRDA Server into appropriate Oracle data types before passing these values back to the application or Oracle tool.

The following table lists the data type mapping and restrictions. The DRDA Server data types that are listed in the table are general. Refer to documentation for your DRDA database for restrictions on data type size and value limitations.

Table 13-6 data type Mapping and Restrictions

DRDA Server | Oracle External | Criteria
--- | --- | ---
CHAR(N) | CHAR(N) | N < = 255
VARCHAR (N) | VARCHAR2(N) LONG | N < = 2000 2000 < N < = 32740
LONG VARCHAR(N) | VARCHAR2(N) | N < = 2000
LONG VARCHAR(N) | LONG | 2000 < N < = 32740
CHAR(N) FOR BIT DATA | RAW(N) | N < = 255
VARCHAR(N) FOR BIT DATA | RAW(N) | 1 < = N < = 255
VARCHAR(N) FOR BIT DATA | LONG RAW(N) | 255 < N < = 32740
LONG VARCHAR(N) FOR BIT DATA | RAW(N | 1 < = N < = 255
LONG VARCHAR(N) FOR BIT DATA | LONG RAW(N) | 255 < N < = 32740
DATE | DATE | Refer to Performing Date and Time Operations
TIME | CHAR(8) | See Performing Date and Time Operations
TIMESTAMP | CHAR(26) | See Performing Date and Time Operations
GRAPHIC | CHAR(2N) | N <= 127
VARGRAPHIC | VARCHAR2(2N) LONG | N <= 1000 1000 <= N <= 16370
LONG VARGRAPHIC | VARCHAR2(2N) LONG | N <= 1000 1000 <= N <= 16370
Floating Point Single | FLOAT(21) | n/a
Floating Point Double | FLOAT(53) | n/a
Decimal (P, S) | NUMBER(P,S) | n/a

### Performing Character String Operations

The gateway performs all character string comparisons, concatenations, and sorts using the data type of the referenced columns, and determines the validity of character string values passed by applications using the gateway. The gateway automatically converts character strings from one data type to another and converts between character strings and dates when needed.

Frequently, DRDA databases are designed to hold non-character binary data in character columns. Applications executed on DRDA systems can generally store and retrieve data as though it contained character data. However, when an application accessing this data runs in an environment that uses a different character set, inaccurate data may be returned.

With the gateway running on the host, character data retrieved from a DB2/400, DB2/OS390, or DB2/VM host is translated from `EBCDIC` to ASCII. When character data is sent to DB2/400, DB2/OS390, or DB2/VM from the host, ASCII data is translated to EBCDIC. When the characters are binary data in a character column, this translation causes the application to receive incorrect information or errors. To resolve these errors, character columns on DB2/400, DB2/OS390, or DB2/VM that hold non-character data must be created with the `FOR BIT DATA` option. In the application, the character columns holding non-character data should be processed using the Oracle data types `RAW` and `LONG RAW`. The `DESCRIBE` information for a character column defined with `FOR BIT DATA` on the host always indicates `RAW` or `LONG RAW`.

### Converting Character String Data types

The gateway binds character string data values from host variables as fixed-length character strings. The bind length is the length of the character string data value. The gateway performs this conversion on every bind.

The `DRDA VARCHAR` data type can be from 1 to 32740 bytes in length. This data type is converted to an Oracle `VARCHAR2` data type if it is between 1 and 2000 characters in length. If it is between 2000 and 32740 characters in length, then it is converted to an Oracle `LONG` data type.

The `DRDA VARCHAR` data type can be no longer than 32740 bytes, which is much shorter than the maximum size for the Oracle `LONG` data type. If you define an Oracle `LONG` data type larger than 32740 bytes in length, then you receive an error message when it is mapped to the `DRDA VARCHAR` data type.

### Performing Graphic String Operations

DB2 `GRAPHIC` data types store only double-byte string data. Sizes for DB2 `GRAPHIC` data types typically have maximum sizes that are half that of their Character counterparts. For example, the maximum size of a `CHAR` may be 255 characters, whereas the maximum size of a `GRAPHIC` may be 127 characters.

Oracle Database does not have a direct matching data type, and the gateway therefore converts between Oracle character data types to DB2 Graphic data types. Oracle Database character data types may contain single, mixed, or double-byte character data. The gateway converts the string data into appropriate double-byte-only format depending upon whether the target DB2 column is a `Graphic` type and whether Gateway Initialization parameters are set to perform this conversion. For more configuration information, refer to Appendix C, "DRDA-Specific Parameters" and Appendix D, "National Language Support".

### Performing Date and Time Operations

The implementation of date and time data differs significantly in IBM DRDA databases and Oracle Database. Oracle Database has a single date data type, `DATE`, that can contain both calendar date and time of day information.

IBM DRDA databases support the following three distinct date and time data types:

DATE is the calendar date only.

TIME is the time of day only.

TIMESTAMP is a numerical value combining calendar date and time of day with microsecond resolution in the internal format of the IBM DRDA database.

#### Processing TIME and TIMESTAMP Data

There is no built-in mechanism that translates the IBM TIME and `TIMESTAMP` data to Oracle `DATE` data. An application must process `TIME` data types to the Oracle `CHAR` format with a length of eight bytes. An application must process the `TIMESTAMP` data type in the Oracle `CHAR` format with a length of 26 bytes.

An application reads `TIME` and `TIMESTAMP` functions as character strings and converts or subsets portions of the string to perform numerical operations. `TIME` and `TIMESTAMP` values can be sent to an `IBM DRDA` database as character literals or bind variables of the appropriate length and format.

#### Processing DATE Data

Oracle and IBM `DATE` data types are mapped to each other. If an IBM DATE is queried, then it is converted to an Oracle `DATE` with a zero (midnight) time of day. If an Oracle `DATE` is processed against an `IBM DATE` column, then the date value is converted to the IBM `DATE` format, and any time value is discarded.

Character representations of dates are different in Oracle format and IBM DRDA format. When an Oracle application SQL statement contains a date literal, or conveys a date using a character bind variable, the gateway must convert the date to an IBM DRDA compatible format.

The gateway does not automatically recognize when a character value is being processed against an IBM `DATE` column. Applications are required to distinguish character date values by enclosing them with Oracle `TO_DATE` function notation. For example, if `EMP` is a synonym or view that accesses data on an IBM DRDA database, then you should notb use the following SQL statement:

    SELECT * FROM EMP WHERE HIREDATE = '03-MAR-81'

you should use the following:

    SELECT * FROM EMP WHERE HIREDATE = TO_DATE('03-MAR-81')

In a programmatic interface program that uses a character bind variable for the qualifying date value, you must use this SQL statement:

    SELECT * FROM EMP WHERE HIREDATE = TO_DATE(:1)

The above SQL notation does not affect SQL statement semantics when the statement is executed against an Oracle Database table. The statement remains portable across Oracle and IBM DRDA-accessed data stores.

The `TO_DATE` function is not required for dates in any of the following formats:

- `YYYY-MM-DD` (ISO/JIS)

- `DD.MM.YYYY` (European)

- `MM/DD/YYYY` (USA)

For example:

    SELECT * FROM EMP WHERE HIREDATE = '1981-03-03'

The `TO_DATE` requirement also does not pertain to input bind variables that are in Oracle date 7-byte binary format. The gateway recognizes such values as dates.

#### Performing Date Arithmetic

The following forms of SQL expression generally do not work correctly with the gateway:

    date + number
    number + date
    date - number
    date1 - date2

The date and number addition and subtraction `(date + number,number + date,date - number)` forms are sent through to the DRDA Server, where they are rejected. The supported servers do not permit number addition or subtraction with dates.

Because of differing interpretations of date subtraction in the supported servers, subtracting two dates `(date1 - date2)` gives results that vary by server.

Note: Avoid date arithmetic expressions in all gateway SQL until date arithmetic problems are resolved.

### Dates

Date handling has two categories:

- Two-digit year dates, which are treated as occurring 50 years before or 50 years after the year 2000.

- Four-digit year dates, which are not ambiguous with regard to the year 2000.

Oracle recommends that you set the Oracle Database 10g server and gateway default `HS_NLS_DATE_FORMAT` parameter to a format including a four-digit year.

Use one of the following methods to enter twenty-first century dates:

- The `TO_DATE` function

  Use any date format including a four character year field. Refer to the Oracle Database SQL Reference for the available date format string options.

  For example, `TO_DATE`('`2008-07-23', 'YYYY-MM-DD`') can be used in any SELECT, INSERT, UPDATE, or DELETE statement.

- The `HS_NLS_DATE_FORMAT` parameter

  The `HS_NLS_DATE_FORMAT` parameter defines a default format for Oracle Database explicit `TO_DATE` functions without a pattern and for implicit string to date conversions.

  For example, with `HS_NLS_DATE_FORMAT` defined as '`YYYY-MM-DD`', '2008-07-23' can be used in any `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.

### HS_NLS_DATE_FORMAT Support

The following table lists the four patterns that can be used for the `HS_NLS_DATE_FORMAT`.

DB2 Date Format | Pattern | Example
--- | --- | ---
EUR | DD.MM.YYYY | 30.10.1994
ISO | YYYY-MM-DD | 1994-10-30
JIS | YYYY-MM-DD | 1994-10-30
USA | MM/DD/YYYY | 10/30/1994

The Oracle Database default format of '`DD-MON-YY`' is not permitted with DB2.

The following example demonstrates how to enter and select date values in the twenty-first century:

    ALTER SESSION SET HS_NLS_DATE_FORMAT = 'YYYY-MM-DD';
    INSERT INTO EMP (HIREDATE) VALUES ('2008-07-23');
    SELECT * FROM EMP WHERE HIREDATE = '2008-07-23';
    UPDATE EMP SET HIREDATE = '2008-07-24'
       WHERE HIREDATE = '2008-07-23';
    DELETE FROM EMP WHERE HIREDATE = '2008-07-24';

### Oracle TO_DATE Function

The Oracle `TO_DATE` function is preprocessed in SQL `INSERT`, `UPDATE`, `DELETE`, and `SELECT` `WHERE` clauses. `TO_DATE` functions in `SELECT` result lists are not preprocessed.

The `TO_DATE` function is often needed to provide values to update or compare with date columns. Therefore, the gateway replaces the information included in the `TO_DATE` clause with an acceptable value before the SQL statement is sent to DB2.

Except for the `SELECT` result list, all `TO_DATE` functions are preprocessed and turned into values that are the result of the `TO_DATE` function. Only `TO_DATE`(`literal`) or `TO_DATE``(:bind_variable)` is permitted. Except in `SELECT` result lists, the `TO_DATE``(column_name)` function format is not supported.

The preprocessing of the Oracle `TO_DATE` functions into simple values is useful in an `INSERT` `VALUES` clause because DB2 does not allow functions in the `VALUES` clause. In this case, DB2 receives a simple value in the `VALUES` list. All forms of the `TO_DATE` function (with one, two, or three operands) are supported.

### Performing Numeric data type Operations

IBM versions of the DRDA Server perform automatic conversions to the numeric data type of the destination column (such as integer, double-precision floating point, or decimal). The user has no control over the data type conversion, and this conversion can be independent of the data type of the destination column in the database.

For example, if `PRICE` is an integer column of the `PRODUCT` table in an IBM DRDA database, then the update shown in the following example inaccurately sets the price of an ice cream cone to $1.00 because the IBM DRDA Server automatically converts a floating point to an integer:

    UPDATE PRODUCT
    SET PRICE = 1.50
    WHERE PRODUCT_NAME = 'ICE CREAM CONE    ';

Because `PRICE` is an integer, the IBM DRDA Server automatically converts the decimal data value of 1.50 to 1.

### Mapping the COUNT Function

Oracle Database supports the following four operands for the `COUNT` function:

- `COUNT(*)`

- `COUNT(DISTINCT colname)`

- `COUNT(ALL colname)`

- `COUNT(colname)`

Some DRDA servers do not support all forms of `COUNT`, specifically `COUNT(colname)` and `COUNT(ALL colname)`. In those cases the `COUNT` function and its arguments are translated into `COUNT`(*). This may not yield the desired results, especially if the column being counted contains `NULLs`.

For those DRDA servers that do not support the above forms, it may be possible to achieve equivalent functionality by adding a `WHERE` clause. For example,

    SELECT COUNT(colname) FROM table@dblink WHERE colname IS NOT NULL

or

    SELECT COUNT(ALL colname) FROM table@dblink WHERE colname IS NOT NULL

Refer to Chapter 2, "SQL Limitations" for known DRDA servers which do not support all forms of `COUNT`.

### Performing Zoned Decimal Operations

A zoned decimal field is described as packed decimal on Oracle Database. However, an Oracle application such as a Pro*C program can insert into a zoned decimal column using any supported Oracle numeric data type. The gateway converts this number into the most suitable data type. Data can be fetched from a DRDA database into any Oracle data type, provided that it does not result in a loss of information.

## Passing Native SQL Statements through the Gateway

The passthrough SQL feature enables an application developer to send a SQL statement directly to the DRDA Server without the statement being interpreted by Oracle Database. `DBMS_HS_PASSTHROUGH`.`EXECUTE_IMMEDIATE` SQL passthrough statements that are supported by the gateway are limited to nonqueries (`INSERT`, `UPDATE`, `DELETE`, and DDL statements) and cannot contain bind variables. The gateway can run native SQL statements using `DBMS_HS_PASSTHROUGH`.`EXECUTE_IMMEDIATE`.

`DBMS_HS_PASSTHROUGH`.`EXECUTE_IMMEDIATE` is a built-in gateway function. This function receives one input argument and returns the number of rows affected by the SQL statement. For data definition language (DDL) statements, the function returns zero.

`DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE` are reserved names of the gateway and are used specifically for running native SQL.

The 10.2 release of Oracle Transparent Gateway for DRDA enables retrieval of result sets from queries issued with passthrough. The syntax is different from the `DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE` function. Refer to "Retrieving Results Sets Through Passthrough" for more information.

### Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

To run a passthrough SQL statement using `DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE`, use the following syntax:

    number_of_rows = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('native_DRDA_sql');

where:

`number_of_rows` is a variable that is assigned the number of rows affected by the passthrough SQL completion. For DDL statements, a zero is returned for the number of rows affected.

`dblink` is the name of the database link used to access the gateway.

`native_DRDA_sql` is a valid nonquery SQL statement (except `CONNECT`, `COMMIT`, and `ROLLBACK`). The statement cannot contain bind variables. Native SQL statements that cannot be dynamically prepared are rejected by the DRDA Server. The SQL statement passed by the `DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE` function must be a character string. For more information regarding valid SQL statements, refer to the SQL Reference for the particular DRDA Server.

#### Examples

1. Insert a row into a DB2 table using `DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE`:

       DECLARE
         num_rows integer;
       BEGIN
       num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
       ('INSERT  INTO SCOTT.DEPT VALUES (10,''PURCHASING'',''PHOENIX'')');
       END;
       /

2. Create a table in DB2 using `DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE`:

       DECLARE
         num_rows integer;
       BEGIN
         num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
         ('CREATE TABLE MYTABLE (COL1 INTEGER, COL2 INTEGER, COL3 CHAR(14),
         COL4 VARCHAR(13))');
       END;
       /

### Retrieving Results Sets Through Passthrough

Oracle Transparent Gateway for DRDA provides a facility to retrieve results sets from a `SELECT` SQL statement entered through passthrough. Refer to Oracle Database Heterogeneous Connectivity Administrator's Guide for additional information.

#### Example

    DECLARE
      CRS binary_integer;
      RET binary_integer;
      VAL VARCHAR2(10)
    BEGIN
      CRS:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@gtwlink;
      DBMS_HS_PASSTHROUGH.PARSE@gtwlink(CRS,'SELECT NAME FROM PT_TABLE');
    BEGIN
      RET:=0;
      WHILE (TRUE)
      LOOP
        RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@gtwlink(CRS,FALSE);
        DBMS_HS_PASSTHROUGH.GET_VALUE@gtwlink(CRS,1,VAL);
        INSERT INTO PT_TABLE_LOCAL VALUES(VAL);
      END LOOP;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          BEGIN
            DBMS_OUTPUT.PUT_LINE('END OF FETCH');
            DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@gtwlink(CRS);
          END;
        END;
    END;
    /

## Oracle Data Dictionary Emulation on a DRDA Server

The gateway optionally augments the DRDA database catalogs with data dictionary views modeled on the Oracle data dictionary. These views are based on the dictionary tables in the DRDA database, presenting that catalog information in views familiar to Oracle users. The views created during the installation of the gateway automatically limit the data dictionary information presented to each user based on the privileges of that user.

### Using the Gateway Data Dictionary

The gateway data dictionary views provide users with an Oracle-like interface to the contents and use of the DRDA database. Some of these views are required by Oracle products. The gateway supports the DB2/OS390, DB2/400, and DB2/UDB catalog views. DB2/VM catalog views are not available.

You can query the gateway data dictionary views to see the objects in the DRDA database and to determine the authorized users of the DRDA database. Many Oracle catalog views are supported by the Oracle Transparent Gateway for DRDA. Refer to Appendix A for descriptions of Oracle DB2 catalog views. These views are completely compatible with the gateway.

### Using the DRDA Catalog

Each DRDA database has its own catalog tables and views, which you might find useful. Refer to the appropriate IBM documentation for descriptions of these catalogs.

## Defining the Number of DRDA Cursors

You can define any number of cursors depending on your application requirements. Oracle recommends that you use the default value of 100. However, if the default is not appropriate for your application, there are two points to consider when defining the number of cursors for your installation:

- Each cursor requires an additional amount of storage and additional management.

- If you change `DRDA_PACKAGE_SECTIONS`, you must rebind the package.

The parameter `DRDA_PACKAGE_SECTIONS` is specific to the DRDA package. This parameter defines the number of sections (open cursors at the IBM database). Refer to Appendix C, "DRDA-Specific Parameters" for more information about setting the `DRDA_PACKAGE_SECTIONS` parameter.